package com.gsmr.controller;

import java.io.File;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.gsmr.entity.BS_info;
import com.gsmr.entity.PageBean;
import com.gsmr.service.BS_infoService;
import com.gsmr.service.Balise_infoService;
import com.gsmr.util.ExcelUtil;
import com.gsmr.util.FormatDate;
import com.gsmr.util.PropertityUtil;
import com.gsmr.util.ResponseUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


@Controller
@RequestMapping("/bs_info")
public class BsInfoController {

	@Resource
	private BS_infoService bsInfoService;

	/**
	 * 分页条件查询bs_info
	 */
	@RequestMapping("/list")
	public void list(@RequestParam(value = "page", required = false) String page, @RequestParam(value = "rows", required = false) String rows, 
			 HttpServletResponse response) throws Exception {
		PageBean pageBean = new PageBean(Integer.parseInt(page), Integer.parseInt(rows));
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");
		map.put("start", pageBean.getStart());
		map.put("size", pageBean.getPageSize());
		List<BS_info> bsInfoList = bsInfoService.find(map);
//		System.out.println("==============="+bsInfoList.get(0).getEquipmentmanufacturer()+"==============="+bsInfoList.get(0).getDeliverytime()+"========"+bsInfoList.get(0).getMaintenancerecords());
		Long total = bsInfoService.getTotal(map);
		JSONObject result = new JSONObject();
		JSONArray jsonArray = JSONArray.fromObject(bsInfoList);
//		System.out.println("jsonArray:==========="+jsonArray.toString());
		result.put("rows", jsonArray);
		result.put("total", total);
		ResponseUtil.write(response, result);
	}


	/**
	 * 添加或者修Balise_info
	 * @param flag 1 添加 2修改
	 */
	@RequestMapping("/save")
	public void save(BS_info bsInfo, HttpServletResponse response, Integer flag) throws Exception {
		int resultTotal = 0; // 操作的记录条数
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");
		map.put("RegionId", bsInfo.getRegionId());
		map.put("FrontProcessorId", bsInfo.getFrontProcessorId());
		map.put("RouteName", bsInfo.getRouteName());
		map.put("Provider", bsInfo.getProvider());
		map.put("BSName", bsInfo.getBSName());
		map.put("KilometerIndicator", bsInfo.getKilometerIndicator());
		map.put("Latitude", bsInfo.getLatitude());
		map.put("Longitude", bsInfo.getLongitude());
		map.put("CGI", bsInfo.getCGI());
		map.put("LAC", bsInfo.getLAC());
		map.put("CI", bsInfo.getCI());
		map.put("RAC", bsInfo.getRAC());
		map.put("BSIC", bsInfo.getBSIC());
		map.put("CarrierFrequencyNumber", bsInfo.getCarrierFrequencyNumber());
		map.put("BCCH", bsInfo.getBCCH());
		map.put("TCH1", bsInfo.getTCH1());
		map.put("TCH2", bsInfo.getTCH2());
		map.put("TCH3", bsInfo.getTCH3());
		map.put("OwnershipBSC", bsInfo.getOwnershipBSC());
		map.put("BSGroupRing", bsInfo.getBSGroupRing());
		map.put("TrafficModle", bsInfo.getTrafficModle());
		map.put("IPAddress", bsInfo.getIPAddress());
		map.put("Fres", bsInfo.getFres());
		
		map.put("Equipmentmanufacturer", bsInfo.getEquipmentmanufacturer());
		map.put("model", bsInfo.getModel());
		map.put("Deliverytime", bsInfo.getDeliverytime());
		map.put("Maintenancerecords", bsInfo.getMaintenancerecords());

		if (flag == 1) {
			resultTotal = bsInfoService.add(map);
		} else {
			resultTotal = bsInfoService.update(map);
		}
		JSONObject result = new JSONObject();
		if (resultTotal > 0) {
			result.put("success", true);
		} else {
			result.put("success", false);
		}
		ResponseUtil.write(response, result);
	}

	/**
	 * 删除Balise_info
	 */
	@RequestMapping("/delete")
	public void delete(@RequestParam(value = "bsIds") String ids, HttpServletResponse response) throws Exception {
		String[] idsStr = ids.split(",");
		Map<String, Object> map = new HashMap<>();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");

		JSONObject result = new JSONObject();
		
		for (int i = 0; i < idsStr.length; i++) {
			map.put("RegionId", idsStr[i]);
			try {
				bsInfoService.delete(map);
			} catch (Exception e) {
				e.printStackTrace();
				result.put("success", false);
				ResponseUtil.write(response, result);
				return;
			}
		}
		result.put("success", true);
		ResponseUtil.write(response, result);
	}


	/**
	 * 导出Balise_info
	 */
	@RequestMapping("/export")
	public void export(@RequestParam(value = "bsIds") String ids, HttpServletResponse response) throws Exception {
		
		Map<String, Object> map = new HashMap<>();
		Workbook wb = new HSSFWorkbook();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");
		
		String headers[] = {"RegionId", "FrontProcessorId", "RouteName", "Provider", "BSName", "KilometerIndicator", "Latitude", "Longitude", "CGI", "LAC", "CI", 
				"RAC", "BSIC", "CarrierFrequencyNumber", "BCCH", "TCH1", "TCH2", "TCH3", "OwnershipBSC", "BSGroupRing", "TrafficModle", "IPAddress","Fres", "Equipmentmanufacturer", "model", "Deliverytime","Maintenancerecords"};
		
		if (ids != null && !ids.equals("null") && ids.length() > 0) {
			map.put("RegionIds", Arrays.asList(ids.split(",")));
		} else {
			map.put("RegionIds", null);
		}

		List<BS_info> bsInfoList = bsInfoService.findByRegionIds(map);
		try {
			ExcelUtil.fillExcelDataBsInfo(bsInfoList, wb, headers);
			ResponseUtil.export(response, wb, "导出bs_info" + FormatDate.formatDate() + ".xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * bs_info上传
	 */
	@RequestMapping("/upload")
	public void upload(HttpServletRequest request, HttpServletResponse response, Integer uploadFlag) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");
		map.put("RegionId", "0 OR 1=1");
		if (uploadFlag == 2) {
			bsInfoService.delete(map);
		}
		
		// 将当前上下文初始化给 CommonsMutipartResolver （多部分解析器）
		CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
		// 检查form中是否有enctype="multipart/form-data"
		if (multipartResolver.isMultipart(request)) {
			// 将request变成多部分request
			MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
			// 获取multiRequest 中所有的文件名
			Iterator iter = multiRequest.getFileNames();

			while (iter.hasNext()) {
				// 一次遍历所有文件
				MultipartFile file = multiRequest.getFile(iter.next().toString());
				if (file != null) {
					//String path = file.getOriginalFilename();
					String path = "已导入" + new Date().getTime() + file.getOriginalFilename();
					// 上传
					File newFile = new File(path);
					file.transferTo(newFile);
					// 写数据进数据库
					POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(newFile));
					newFile.delete();
					HSSFWorkbook wb = new HSSFWorkbook(fs);
					HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
					JSONObject result = new JSONObject();
					try {
						if (hssfSheet != null) {
							for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
								HSSFRow hssfRow = hssfSheet.getRow(rowNum);
								if (hssfRow == null) {
									continue;
								}
								
								String RegionId = ExcelUtil.formatCell(hssfRow.getCell(0));
								if (RegionId.length() < 1 || existRegionId(RegionId, null)) {
									System.out.println("RegionId: " + RegionId + "已存在，跳过该行。");
									continue;
								}
								map.put("RegionId", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(0))));
								
								
								map.put("FrontProcessorId", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(1))).trim());
								map.put("RouteName", ExcelUtil.formatCell(hssfRow.getCell(2)).trim());
								map.put("Provider", ExcelUtil.formatCell(hssfRow.getCell(3)).trim());
								map.put("BSName", ExcelUtil.formatCell(hssfRow.getCell(4)).trim());
								map.put("KilometerIndicator", ExcelUtil.formatCell(hssfRow.getCell(5)).trim());
								map.put("Latitude", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(6))).trim());
								map.put("Longitude", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(7))).trim());
								map.put("CGI", ExcelUtil.formatCell(hssfRow.getCell(8)).trim());
								map.put("LAC", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(9))).trim());
								map.put("CI", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(10))).trim());
								map.put("RAC", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(11))).trim());
								map.put("BSIC", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(12))).trim());
								map.put("CarrierFrequencyNumber", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(13))).trim());
								map.put("BCCH", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(14))).trim());
								map.put("TCH1", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(15))).trim());
								map.put("TCH2", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(16))).trim());
								map.put("TCH3", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(17))).trim());
								map.put("OwnershipBSC", ExcelUtil.formatCell(hssfRow.getCell(18)).trim());
								map.put("BSGroupRing", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(19))).trim());
								map.put("TrafficModle", ExcelUtil.getIntegerPart(ExcelUtil.formatCell(hssfRow.getCell(20))).trim());
								map.put("IPAddress", ExcelUtil.formatCell(hssfRow.getCell(21)).trim());
								map.put("Fres", ExcelUtil.formatCell(hssfRow.getCell(22)).trim());
							    map.put("Equipmentmanufacturer", ExcelUtil.formatCell(hssfRow.getCell(23)).trim());
							    map.put("model", ExcelUtil.formatCell(hssfRow.getCell(24)).trim());
								map.put("Deliverytime", ExcelUtil.formatCell(hssfRow.getCell(25)).trim());
								map.put("Maintenancerecords", ExcelUtil.formatCell(hssfRow.getCell(26)).trim());
								bsInfoService.add(map);
							}
						}
						result.put("success", true);
					} catch (Exception e) {
						result.put("success", false);
						e.printStackTrace();
					}
					ResponseUtil.write(response, result);
				}
			}
		}
	}

	
	/**
	 * 判断是否存在指定用户名
	 */
	@RequestMapping("/existRegionId")
	public boolean existRegionId(String RegionId, HttpServletResponse response) throws Exception {
		JSONObject result = new JSONObject();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("database_name", PropertityUtil.GSMR_DATABASE);
		map.put("table_name", "bs_info");
		map.put("RegionId", RegionId);
		
		boolean hasDuplicate = false;
		if (bsInfoService.findByRegionId(map) == null) {
			result.put("exist", false);
		} else {
			result.put("exist", true);
			hasDuplicate = true;
		}
		if (response != null)
			ResponseUtil.write(response, result);
		
		return hasDuplicate;
		
	}
}